﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Globalization;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using MockPrjTeam4.Entity;
using MockPrjTeam4.Common;

namespace MockPrjTeam4.DAO
{
    public class ContactsDAO
    {
        /// <summary>
        /// Method to get Contact Object
        /// </summary>
        /// <param name="read"></param>
        /// <returns></returns>
        public static Contacts ContactObjec(IDataRecord read)
        {
            if (read == null)
                return null;
            return new Contacts
            {
                ContactID = Methods.ConvertNumber(read[Contacts.FieldContactID].ToString()),
                FirstName = Methods.ConvertString(read[Contacts.FieldFirstName].ToString()),
                SurName = Methods.ConvertString(read[Contacts.FieldSurName].ToString()),
                Alias = Methods.ConvertString(read[Contacts.FieldAlias].ToString()),
                OfficePhone = Methods.ConvertString(read[Contacts.FieldOfficePhone].ToString()),
                MobilePhone = Methods.ConvertString(read[Contacts.FieldMobilePhone].ToString()),
                HomePhone = Methods.ConvertString(read[Contacts.FieldHomePhone].ToString()),
                Email = Methods.ConvertString(read[Contacts.FieldEmail].ToString()),
                ManagerID = Methods.ConvertNumber(read[Contacts.FieldManagerID].ToString()),
                ContactType = Methods.ConvertString(read[Contacts.FieldContactType].ToString()),
                BestContactMethod = Methods.ConvertString(read[Contacts.FieldBestContactMethod].ToString()),
                JobRole = Methods.ConvertString(read[Contacts.FieldJobRole].ToString()),
                WorkBase = Methods.ConvertString(read[Contacts.FieldWorkBase].ToString()),
                JobTitle = Methods.ConvertString(read[Contacts.FieldJobTitle].ToString()),
                IsActive = Methods.ConvertBoolean(read[Contacts.FieldIsActive].ToString()),
            };
        }

        /// <summary>
        /// Method to get table of Contacts
        /// </summary>
        /// <returns></returns>
        public static DataTable ViewContact()
        {
            var query = string.Format(CultureInfo.CurrentCulture,
                "SELECT * FROM Contacts");
            return SqlHelper.ExecuteQuery(query);
        }

        public static DataTable SearchContact(Contacts contact)
        {
            var query = string.Format(CultureInfo.CurrentCulture, "SELECT * FROM Contacts");
            return SqlHelper.ExecuteQuery(query);
        }
        /// <summary>
        /// Method to add a Contact
        /// </summary>
        /// <param name="contact"></param>
        /// <returns></returns>
        public static bool AddContacts(Contacts contact)
        {
            var query = string.Format(CultureInfo.CurrentCulture,
                "INSERT INTO {0}({1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14})" +
                "VALUES(@FirstName, @SurName, @Alias, @OfficePhone, @MobilePhone, @HomePhone, @Email, " +
                "@ManagerName, @ContactType, @BestContactMethod, @JobRole, @WorkBase, @JobTitle, @IsActive)",
                ScheduleEntities.TableContact,
                Contacts.FieldFirstName,
                Contacts.FieldSurName,
                Contacts.FieldAlias,
                Contacts.FieldOfficePhone,
                Contacts.FieldMobilePhone,
                Contacts.FieldHomePhone,
                Contacts.FieldEmail,
                Contacts.FieldManagerID,
                Contacts.FieldContactType,
                Contacts.FieldBestContactMethod,
                Contacts.FieldJobRole,
                Contacts.FieldWorkBase,
                Contacts.FieldJobTitle,
                Contacts.FieldIsActive
                );
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@FirstName", contact.FirstName),
                new SqlParameter("@SurName", contact.SurName),
                new SqlParameter("@Alias", contact.Alias),
                new SqlParameter("@OfficePhone", contact.OfficePhone),
                new SqlParameter("@MobilePhone", contact.MobilePhone),
                new SqlParameter("@HomePhone", contact.HomePhone),
                new SqlParameter("@Email", contact.Email),
                new SqlParameter("@ManagerName", contact.ManagerID),
                new SqlParameter("@ContactType", contact.ContactType),
                new SqlParameter("@BestContactMethod", contact.BestContactMethod),
                new SqlParameter("@JobRole", contact.JobRole),
                new SqlParameter("@WorkBase", contact.WorkBase),
                new SqlParameter("@JobTitle", contact.JobTitle),
                new SqlParameter("@IsActive", contact.IsActive),
            };


            SqlHelper.ExecuteQuery(query, parameter);
            return true;
        }

        /// <summary>
        /// Method to edit a contact
        /// </summary>
        /// <param name="contact"></param>
        /// <returns></returns>
        public static bool EditContacts(Contacts contact)
        {
            var query = string.Format(CultureInfo.CurrentCulture,
                "UPDATE {0} SET {1} = @FirstName, {2} = @SurName, {3} = @Alias, {4} = @OfficePhone," +
                "{5} = @MobilePhone, {6} = @HomePhone, {7} = @Email, {8} = @ManagerName, " + 
                "{9} = @ContactType, {10} = @BestContactMethod, {11} = @JobRole, {12} = @WorkBase," +
                "{13} = @JobTitle, {14} = @IsActive WHERE ContactId = @ContactId",                
                ScheduleEntities.TableContact,
                Contacts.FieldFirstName,
                Contacts.FieldSurName,
                Contacts.FieldAlias,
                Contacts.FieldOfficePhone,
                Contacts.FieldMobilePhone,
                Contacts.FieldHomePhone,
                Contacts.FieldEmail,
                Contacts.FieldManagerID,
                Contacts.FieldContactType,
                Contacts.FieldBestContactMethod,
                Contacts.FieldJobRole,
                Contacts.FieldWorkBase,
                Contacts.FieldJobTitle,
                Contacts.FieldIsActive
                );
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@FirstName", contact.FirstName),
                new SqlParameter("@SurName", contact.SurName),
                new SqlParameter("@Alias", contact.Alias),
                new SqlParameter("@OfficePhone", contact.OfficePhone),
                new SqlParameter("@MobilePhone", contact.MobilePhone),
                new SqlParameter("@HomePhone", contact.HomePhone),
                new SqlParameter("@Email", contact.Email),
                new SqlParameter("@ManagerName", contact.ManagerID),
                new SqlParameter("@ContactType", contact.ContactType),
                new SqlParameter("@BestContactMethod", contact.BestContactMethod),
                new SqlParameter("@JobRole", contact.JobRole),
                new SqlParameter("@WorkBase", contact.WorkBase),
                new SqlParameter("@JobTitle", contact.JobTitle),
                new SqlParameter("@IsActive", contact.IsActive),
                new SqlParameter("@ContactId", contact.ContactID),
            };
            try
            {
                SqlHelper.ExecuteNonQuery(query, parameter);
                return true;
            }
            catch(SqlException ex)
            {
                return false;
            }
        }

        public static List<Contacts> GetListContacts()
        {
            List<Contacts> contact = new List<Contacts>();

            string query = string.Format("SELECT * FROM Contacts ORDER BY FirstName");

            using (IDataReader read = SqlHelper.ExecuteReader(query))
            {
                while (read.Read())
                {
                    contact.Add(ContactObjec(read));
                }
            }

            return contact;
        }

        public static List<Contacts> GetListActiveContacts()
        {
            List<Contacts> contact = new List<Contacts>();

            string query = string.Format("SELECT * FROM Contacts WHERE IsActive = 'true' ORDER BY FirstName");
            
            using (IDataReader read = SqlHelper.ExecuteReader(query))
            {
                while (read.Read())
                {
                    contact.Add(ContactObjec(read));                    
                }                
            }

            return contact;
        }
        public static IList<Contacts> Search(string firstName, string surname, string isActive)
        {
            IList<Contacts> contact = new List<Contacts>();
            string sql = string.Format(CultureInfo.CurrentCulture,
                                       @"select * from {0} where
                 {1} like '%'+@Firstname+'%' AND {2} like '%'+@Surname+'%' AND {3}=@IsActive" ,
                                       ScheduleEntities.TableContact,
                                       Contacts.FieldFirstName,
                                       Contacts.FieldSurName,
                                       Contacts.FieldIsActive);
            var list = new Collection<SqlParameter>
                           {
                               new SqlParameter("@Firstname", firstName),
                               new SqlParameter("@Surname", surname),
                               new SqlParameter("@IsActive", isActive)
                           };
            sql += string.Format(CultureInfo.CurrentCulture, " ORDER BY {0}", Contacts.FieldFirstName);
            using (IDataReader reader = SqlHelper.ExecuteReader(sql, list))
            {
                try
                {
                    while (reader.Read())
                    {
                        contact.Add(ContactObjec(reader));
                    }
                    return contact;
                }
                catch (SqlException ex)
                {
                    return null;
                }
            }
        }

        public static IList<Contacts> Search(string firstName, string surname)
        {
            IList<Contacts> contact = new List<Contacts>();
            string sql = string.Format(CultureInfo.CurrentCulture,
                                       @"select * from {0} where
                 {1} like '%'+@Firstname+'%' AND {2} like '%'+@Surname+'%'",
                                       ScheduleEntities.TableContact,
                                       Contacts.FieldFirstName, 
                                       Contacts.FieldSurName);
            var list = new Collection<SqlParameter>
                           {
                               new SqlParameter("@Firstname", firstName),
                               new SqlParameter("@Surname", surname)
                           };
            sql += string.Format(CultureInfo.CurrentCulture, " ORDER BY {0}", Contacts.FieldFirstName);
            using (IDataReader reader = SqlHelper.ExecuteReader(sql, list))
            {
                try
                {
                    while (reader.Read())
                    {
                        contact.Add(ContactObjec(reader));
                    }
                    return contact;
                }
                catch (SqlException ex)
                {
                    return null;
                }
            }
        }

        public static Contacts GetContactById(int id)
        {
            string query = string.Format(CultureInfo.CurrentCulture, "SELECT * FROM Contacts WHERE ContactId = @ContactId");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@ContactId", id)
            };
            IDataReader read = SqlHelper.ExecuteReader(query, parameter);
            if (read.Read())
            {
                return ContactObjec(read);
            }
            else
            {
                return null;
            }
        }

        public static bool DeleteContact(int id)
        {
            string query = string.Format(CultureInfo.CurrentCulture,
                "DELETE FROM Contacts WHERE ContactId = @ContactId");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@ContactId", id)
            };
            try
            {
                SqlHelper.ExecuteNonQuery(query, parameter);
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
        }

        public static IList<Contacts> SearchFilter(string range, bool inActive)
        {
            IList<Contacts> contact = new List<Contacts>();
            string query = string.Format("SELECT * FROM Contacts WHERE FirstName LIKE @firstName ORDER BY FirstName");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@firstName", range)
            };
            IDataReader read;
            if (inActive == true)
            {
                read = SqlHelper.ExecuteReader(query, parameter);
            }
            else
            {
                query = string.Format("SELECT * FROM Contacts WHERE FirstName LIKE @firstName AND IsActive='true'" +
                    "ORDER BY FirstName");
                read = SqlHelper.ExecuteReader(query, parameter);
            }
            while (read.Read())
            {
                contact.Add(ContactObjec(read));
            }
            return contact;
        }

        public static DataTable GetContactSP()
        {
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@startRowIndex", 1),
                new SqlParameter("@maximumRows", 15),
                new SqlParameter("@totalRows", ParameterDirection.Output)
            };
            return SqlHelper.ExecuteDataAdapter("usp_GetContacts", parameter);
        }
    }
}